Working on your database design with Designer/2000

By David C. Moss

In our May article, we examined how to begin translating your Entity-Relationship model - a logical model - into a physical data model using Designer/2000. This physical data model will become the basis for creat-ing tables, columns, and foreign keys - ultimately generating Data Definition Language (DDL) scripts to be used in creating your database. In this article, we'll discuss relational database theory, and we'll show how to use the Data Diagrammer to examine and modify your table, column, primary key, and foreign key definitions.

The theory of relativity

Before we get into refining your physical database model, it's time for a quick refresher course on relational databases - just to make sure we're all on the same page. If you've spent any time in the relational world, you've probably heard about E. F. Codd, who invented the idea of a relational database. Unfortunately, his writings could only be translated by PhDs from MIT. Fortunately, there's Chris Date. He's kind of like the spiritual medium for Codd. He's also one of the creators of DB2, IBM's implementation of relational database theory. And he wrote one of the easiest-to-understand, yet comprehensive books on relational databases, An Introduction to Database Systems. If you don't have it in your library, you should get it.

Anyway, Mr. Codd came up with twelve rules regarding relational databases. If your database doesn't adhere to these rules, then it isn't relational. Here are the first two:

• The Information Rule - All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables.
• The Guaranteed Rule - Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.

We'll deal with some of the other rules in future articles - but the good news is that Oracle qualifies as a relational database! So let's examine the rest of the relevant information you'll need in order to work on your relational database design.

The basics

A relational database is made up of tables, each of which has a name, one or more columns, and zero or more rows. A primary key uniquely identifies a row in a table and may contain a single column or a combination of several columns. A foreign key references an existing primary key. When all foreign keys refer to primary keys that actually exist they have referential integrity.

A relational database is data-driven, not design-driven. It is designed once, and the data changes over time without affecting either database structure or application logic. Data in a relational database is stored in one place, read from one place, and modified in one place. Data is stored once, so maintaining consistency among all applications is easier. Rules that control how the data will be stored are defined and enforced as part of the relational database.

The details

In our May article, we discussed what you need to do before you execute a utility program - the Database Design Wizard - in Designer/2000. As we noted, in real-life CASE projects - at least the successful ones - you use CASE tools after you spend quality time with your users gaining a complete understanding of the application. As we also noted, running the Database Design Wizard isn't the end of the road in database design. In fact, it's just the beginning. So let's take the table, column, and foreign key definitions generated by the Database Design Wizard and find out how and what details to add.

As you'll recall, in most cases each entity you defined will become a table, each attribute will become a column in a table, and each UID will become a primary key. Relationships to entities will become foreign keys. Some additional things will happen: Optional relationships will create null foreign-key columns, and mandatory relationships will create not-null foreign-key columns.

Since we've used the Database Design Wizard to create most of these definitions for us from our Entity-Relationship model, we've already done a lot of the difficult work. But now we need to make sure the definitions fit the needs of our project. And to do that, we need the right tool.

The Data Diagrammer

Designer/2000's Data Diagrammer is a new feature of Oracle's CASE tools - and a welcome one. This tool allows you to visually see the links between tables and to add and enhance table, column, primary key, and foreign key definitions.

There are many ways to get to the Data Diagrammer. You can get there by clicking the Data Diagrammer icon directly from Windows, or you can first click on the Repository Object Navigator (RON) in Windows and select Data Diagrammer from the Tools menu. Then select New from the Files menu. Doing so will give you a clean slate to work with. But hidden beneath the surface in the Repository are all the table, column, primary key, and foreign key definitions that have already been defined for you. To get to those, select Include Tables/Views/Snapshots from the Files menu. You'll see a list like the one shown in Figure A.

Figure A: The Include Tables/Views/Snapshots screen shows all the table, column, primary key, and foreign key definitions for your application

From the Include Tables/Views/Snapshots screen, you can do some pretty nifty things. For example, if you'd like Designer/2000 to create a data diagram for you from the tables and foreign keys that were created by the Database Design Wizard, click the Select All button, then select Whole Diagram in the Autolayout to radio group and select the Foreign Keys checkbox in the Also Include section.

Once you've selected these items and clicked OK, you'll see a diagram similar to the one shown in Figure B. Each box on the screen represents a table, and each line represents a foreign key. You can select a box by double-clicking on it. Selecting it will bring you to the Edit Table window, which provides separate tabs for editing Table, Column Definition, Column Display, Table Constraints, and Table Validation information. We'll show how to use the Data Diagrammer to refine this and other relevant information in our next article.

Figure B: This diagram shows the relationships between the tables in your application.

Crossing the river from logical to physical models is straightforward if you keep your map and compass with you: The map shows how your physical model matches the business needs identified in your logical model, and the compass - the Change Control process - keeps you heading in the right direction.

Where to go for information

If you'd like to find out more about Oracle's approach to database design, please refer to Appendix F of CASE*Method: Entity-Relationship Modelling by Richard Barker (Oracle/Addison-Wesley, 1989).

David Moss is a Managing Consultant with TrueNorth Consulting, Inc. He has worked with Oracle's CASE tools since 1988, including over three years with Oracle Consulting Group. You can reach David by phone at (503) 220-1790 or by E-mail at truenrth@ix.netcom.com.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.